package casper.alpha.utils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;

import casper.alpha.CasperManager;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.Envelope;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKTReader;

public class CasperQuery {
	/*
	 * TABLE: public_data
	 * id(PK), lat, lng
	 * TABLE: private_data
	 * (id, lat, lng) (PK)
	 */
	
	public void clearPrivateData() {
		System.out.println("Clear Private data");
		try {
			DBConnection.execute("DELETE FROM private_data");
			DBConnection.execute("DELETE FROM reduce_data");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("Private data cleared");
	}
	
	public void clearPublicData() {
		System.out.println("Clear Public data");
		try {
			DBConnection.execute("DELETE FROM public_data");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("Public data cleared");
	}
	
	public void clearAllData() {
		clearPrivateData();
		clearPublicData();
	}
	
	public void insertPublicData(Coordinate c, int id) {
		try {
			DBConnection.execute(String.format("INSERT INTO public_data(lat, lng, location) VALUES(%f, %f, ST_GeomFromText('POINT(%f %f)',4326))", c.x, c.y, c.y, c.x));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void insertPrivateData(Geometry g, int id, boolean reduce) {
		Coordinate[] cs = g.getCoordinates();
		int index = 0;
		//'POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
		//-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))'
		String lineString = "'POLYGON((";
		for(Coordinate c : cs) {
			index++;
			if(index == 1) {
				lineString += String.format("%f %f", c.y, c.x);
			} else {
				lineString += String.format(", %f %f", c.y, c.x);
			}
		}
		lineString += "))'";
		index = 0;
		for(Coordinate c : cs) {
			index++;
			String query = String.format("INSERT INTO %s_data(id, index, lat, lng, location, polygon) VALUES(%d, %d, %f, %f, ST_GeomFromText('POINT(%f %f)',4326), ST_GeomFromText("+lineString+",4326))", reduce ? "reduce" : "private", id, index, c.x, c.y, c.y, c.x);
			try {
				DBConnection.execute(query);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void clearPublicDataCache() {
		publicDataCache.clear();
	}
	
	public void clearPrivateDataCache() {
		privateDataCache.clear();
	}
	
	public void clearDataCache() {
		clearPrivateDataCache();
		clearPublicDataCache();
	}
	
	private static Map<String, List<Coordinate>> publicDataCache = new Hashtable<String, List<Coordinate>>();
	private static Map<String, List<Geometry>> 	privateDataCache = new Hashtable<String, List<Geometry>>();
	private static Map<String, List<Integer>> 	privateDataCacheID = new Hashtable<String, List<Integer>>();
	
	public List<Coordinate> getPublicKNNs(Coordinate c) {
		String key = c.toString();
		if(publicDataCache.containsKey(key)) {
			return publicDataCache.get(key);
		}
		List<Coordinate> knns = new LinkedList<Coordinate>();
		String query = String.format("SELECT id, location, lat, lng " +
									"FROM public_data WHERE lat <> %f AND lng <> %f " +
									"ORDER BY ST_Distance(location, ST_GeomFromText('POINT(%f %f)',4326)) ASC, id ASC LIMIT %d",c.x, c.y, c.y, c.x, CasperManager.getK());
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				double lat = result.getDouble("lat");
				double lng = result.getDouble("lng");
				knns.add(new Coordinate(lat, lng));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		publicDataCache.put(key, knns);
		return knns;
	}
	public Coordinate getMaxDistanceCoordinate(Coordinate c, int id) {
		String query = String.format("SELECT id, index, lat, lng, MAX(ST_DISTANCE(location, ST_GeomFromText('POINT(%f %f)',4326))) " +
									"FROM private_data " +
									"WHERE id = %d", c.y, c.x, id);
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				double lat = result.getDouble("lat");
				double lng = result.getDouble("lng");
				return new Coordinate(lat, lng);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public Coordinate getMaxDistanceCoordinate(Coordinate c, Geometry g) {
		double max = 0;
		Coordinate maxC = null;
		for(Coordinate gc : g.getCoordinates()) {
			double distance = c.distance(gc);
//			System.out.println(distance);
			if(distance > max) {
				max = distance;
				maxC = gc;
			}
		}
		return maxC;
	}
	public Geometry getGeometryById(int id) {
		String query = String.format("SELECT id, index, lat, lng FROM private_data WHERE id = %d ORDER BY index ASC", id);
		try {
			ResultSet result = DBConnection.executeQuery(query);
			String lineString = "";
			boolean valid = false;
			while(result.next()) {
				valid = true;
				double lat = result.getDouble("lat");
				double lng = result.getDouble("lng");
				int index = result.getInt("index");
				if(index == 1) {
					lineString += String.format("%f %f", lat, lng);
				} else {
					lineString += String.format(", %f %f", lat, lng);
				}
			}
			if (!valid) return null;
			//System.out.println(lineString);
			return new WKTReader().read("LINESTRING ("+lineString+")");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	public List<Geometry> getPrivateKNNs(Coordinate c, boolean reduce) {
		String key = c.toString();
		if(privateDataCache.containsKey(key)) {
			return privateDataCache.get(key);
		}
		List<Geometry> knns = new LinkedList<Geometry>();
		/*
		 * SELECT id, MAX(ST_DISTANCE(location, ST_GeomFromText('POINT(100.000000 0.000000)',4326))) as max_dist
			FROM private_data
			GROUP BY id
			ORDER BY max_dist ASC
			LIMIT 3
		 */
		String query = String.format("SELECT id, MAX(ST_DISTANCE(location, ST_GeomFromText('POINT(%f %f)',4326))) as max_dist " +
									"FROM %s_data " +
									"GROUP BY id " +
									"ORDER BY max_dist ASC " +
									"LIMIT %d", c.y, c.x, reduce ? "reduce" : "private", CasperManager.getK());
		List<Integer> knnids = new LinkedList<Integer>();
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				
				int id = result.getInt("id");
				if(!knnids.contains(id)) {
					//System.out.println(id + " : " + result.getDouble("max_dist"));
					knnids.add(id);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for(int id : knnids) {
			knns.add(getGeometryById(id));
		}
		privateDataCache.put(key, knns);
		return knns;
	}
	public List<Integer> getPrivateKNNIDs(Coordinate c, boolean reduce) {
		String key = c.toString();
		if(privateDataCacheID.containsKey(key)) {
			return privateDataCacheID.get(key);
		}
		//List<Integer> knns = new LinkedList<Integer>();
		/*
		 * SELECT id, MAX(ST_DISTANCE(location, ST_GeomFromText('POINT(100.000000 0.000000)',4326))) as max_dist
			FROM private_data
			GROUP BY id
			ORDER BY max_dist ASC
			LIMIT 3
		 */
		String query = String.format("SELECT id, MAX(ST_DISTANCE(location, ST_GeomFromText('POINT(%f %f)',4326))) as max_dist " +
									"FROM %s_data " +
									"GROUP BY id " +
									"ORDER BY max_dist ASC " +
									"LIMIT %d", c.y, c.x, reduce ? "reduce" : "private", CasperManager.getK());
		List<Integer> knnids = new LinkedList<Integer>();
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result != null && result.next()) {
				
				int id = result.getInt("id");
				if(!knnids.contains(id)) {
//					System.out.println(id + " : " + result.getDouble("max_dist"));
					knnids.add(id);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		privateDataCacheID.put(key, knnids);
		return knnids;
	}
	public List<Coordinate> getAllPublicData(Coordinate c, double distance) {
//		System.out.println("d : " + distance);
		List<Coordinate> pd = new LinkedList<Coordinate>();
		//ST_GeomFromText('POINT(52.278888888889 8.0430555555556)',4326)
		String query = String.format("SELECT id, location, lat, lng " +
									"FROM public_data " +
									"WHERE ST_DWithin(location, ST_GeomFromText('POINT(%f %f)',4326), %f)", c.y, c.x, distance);
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				double lat = result.getDouble("lat");
				double lng = result.getDouble("lng");
				pd.add(new Coordinate(lat, lng));
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pd;
	}
	public Coordinate expand(Coordinate c, double offsetLat, double offsetLng) {
		double lat = c.x + offsetLat;
		double lng = c.y + offsetLng;
		if (lat > 90) {
			lat = 90;
		} else if (lat < -90) {
			lat = -90;
		}
		if (lng > 180) {
			lng = 180;
		} else if (lat < -180) {
			lng = -180;
		}
		return new Coordinate(lat, lng);
	}
	public List<Geometry> getAllPrivateData(Coordinate c, double distance, boolean reduce) {
//		System.out.println("d : " + distance);
		List<Geometry> pd = new LinkedList<Geometry>();
		String lineString = "'POLYGON((";
		Coordinate co = expand(c, -distance, -distance);
		lineString += String.format("%f %f", co.y, co.x);
		co = expand(c, -distance, distance);
		lineString += String.format(", %f %f", co.y, co.x);
		co = expand(c, distance, distance);
		lineString += String.format(", %f %f", co.y, co.x);
		co = expand(c, distance, -distance);
		lineString += String.format(", %f %f", co.y, co.x);
		co = expand(c, -distance, -distance);
		lineString += String.format(", %f %f", co.y, co.x);
		lineString += "))'";
		String query = String.format("SELECT DISTINCT(id) FROM %s_data WHERE ST_Intersects(ST_GeomFromText("+lineString+",4326), polygon) ORDER BY id ASC", reduce ? "reduce" : "private");
		List<Integer> knnids = new LinkedList<Integer>();
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				int id = result.getInt("id");
				knnids.add(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for(int id : knnids) {
			pd.add(getGeometryById(id));
		}
		return pd;
	}
	public List<Integer> getAllPrivateDataID(Coordinate c, double distance, boolean reduce) {
//		System.out.println("d : " + distance);
		String lineString = "'POLYGON((";
		Coordinate co = expand(c, -distance, -distance);
		lineString += String.format("%f %f", co.y, co.x);
		co = expand(c, -distance, distance);
		lineString += String.format(", %f %f", co.y, co.x);
		co = expand(c, distance, distance);
		lineString += String.format(", %f %f", co.y, co.x);
		co = expand(c, distance, -distance);
		lineString += String.format(", %f %f", co.y, co.x);
		co = expand(c, -distance, -distance);
		lineString += String.format(", %f %f", co.y, co.x);
		lineString += "))'";
		String query = String.format("SELECT DISTINCT(id) FROM %s_data WHERE ST_Intersects(ST_GeomFromText("+lineString+",4326), polygon) ORDER BY id ASC", reduce ? "reduce" : "private");
		List<Integer> knnids = new LinkedList<Integer>();
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				int id = result.getInt("id");
				if(!knnids.contains(id)) {
					knnids.add(id);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return knnids;
	}
	public List<Coordinate> getAllPublicDataInGeometry(Geometry g) {
		Coordinate[] cs = g.getCoordinates();
		int index = 0;
		//'POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
		//-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))'
		String lineString = "'POLYGON((";
		for(Coordinate c : cs) {
			index++;
			if(index == 1) {
				lineString += String.format("%f %f", c.y, c.x);
			} else {
				lineString += String.format(", %f %f", c.y, c.x);
			}
		}
		lineString += "))'";
		List<Coordinate> pd = new LinkedList<Coordinate>();
		String query = String.format("SELECT id, location, lat, lng FROM public_data WHERE ST_Contains(ST_GeomFromText("+lineString+",4326), location)");
//		System.out.println(query);
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				double lat = result.getDouble("lat");
				double lng = result.getDouble("lng");
				pd.add(new Coordinate(lat, lng));
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pd;
	}
	public List<Geometry> getAllPrivateDataInGeometry(Geometry g, boolean reduce) {
		Coordinate[] cs = g.getCoordinates();
		int index = 0;
		//'POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
		//-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))'
		String lineString = "'POLYGON((";
		for(Coordinate c : cs) {
			index++;
			if(index == 1) {
				lineString += String.format("%f %f", c.y, c.x);
			} else {
				lineString += String.format(", %f %f", c.y, c.x);
			}
		}
		lineString += "))'";
		List<Geometry> pd = new LinkedList<Geometry>();
		String query = String.format("SELECT DISTINCT(id) FROM %s_data WHERE ST_Intersects(ST_GeomFromText("+lineString+",4326), polygon) ORDER BY id ASC", reduce ? "reduce" : "private");
		List<Integer> pdids = new LinkedList<Integer>();
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				int id = result.getInt("id");
				pdids.add(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for(int id : pdids) {
			pd.add(getGeometryById(id));
		}
		return pd;
	}
	public List<Integer> getAllPrivateDataIDInGeometry(Geometry g, boolean reduce) {
		Coordinate[] cs = g.getCoordinates();
		if (reduce) {
			//cs = g.getEnvelope().getCoordinates();
		}
		int index = 0;
		//'POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
		//-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))'
		String lineString = "'POLYGON((";
		for(Coordinate c : cs) {
			index++;
			if(index == 1) {
				lineString += String.format("%f %f", c.y, c.x);
			} else {
				lineString += String.format(", %f %f", c.y, c.x);
			}
		}
		lineString += "))'";
		List<Integer> pd = new LinkedList<Integer>();
		String query = String.format("SELECT DISTINCT(id) FROM %s_data WHERE ST_Intersects(ST_GeomFromText("+lineString+",4326), polygon) ORDER BY id ASC", reduce ? "reduce" : "private");
		//System.out.println(query);
		List<Integer> pdids = new LinkedList<Integer>();
		try {
			ResultSet result = DBConnection.executeQuery(query);
			while(result.next()) {
				int id = result.getInt("id");
				pdids.add(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for(int id : pdids) {
			pd.add(id);
		}
		return pd;
	}
	
	public Coordinate getExactLocationIn(Geometry g) {
		Envelope e = g.getEnvelopeInternal();
		int deltaX = (int)(e.getMaxX() - e.getMinX()) > 0 ? (int)(e.getMaxX() - e.getMinX()) : 1;
		int deltaY = (int)(e.getMaxY() - e.getMinY()) > 0 ? (int)(e.getMaxY() - e.getMinY   ()) : 1;
		double lat = e.getMinX() + (new Random().nextInt(deltaX));
		double lng = e.getMinY() + (new Random().nextInt(deltaY));
		return new Coordinate(lat, lng);
	}
}
